﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using STHPMS1.ICore.I_Entity.Stmpms1;
using STHPMS1.ICore.I_DAL.Repository;
using STHPMS1.DAL.D_Core;
using System.Reflection;

namespace STHPMS1.DAL.D_Repository
{
    public class ExcelRepository : DalBase, IDisposable, IExcelRepository
    {
        SqlConnection conn = null;
        SqlBulkCopy bulkCopy = null;
        long rowsCopiedId = 0;//操作行数
        SqlTransaction tran = null;
        public ExcelRepository()
        {
            string connectionString = D_Dao.ConnectionStringBuilder.ConnectionString;
            conn = new SqlConnection(connectionString);
            conn.Open();
            tran = conn.BeginTransaction();
            bulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, tran);
            bulkCopy.BatchSize = 1000;
            bulkCopy.BulkCopyTimeout = 3600;
            bulkCopy.NotifyAfter = 1;
            bulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(SqlRowsCopied);
        }
        private void SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
        {
            rowsCopiedId = e.RowsCopied;
        }
       
        public new void Dispose()
        {
            Close();
        }
        public void Close()
        {
            if (conn != null && conn.State != ConnectionState.Closed)
                conn.Close();
            if (bulkCopy != null)
                bulkCopy.Close();
        }

        public void ImportAll(string tableName1,DataTable dt_k,string tableName2, DataTable dt_v)
        {
            using (SqlConnection conn = new SqlConnection(D_Dao.ConnectionStringBuilder.ConnectionString))
            {

                SqlTransaction st = null;
                try
                {
                    conn.Open();
                    st = conn.BeginTransaction();
                    using (SqlBulkCopy sqlBC1 = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, st))
                    {
                        sqlBC1.SqlRowsCopied += new SqlRowsCopiedEventHandler(SqlRowsCopied);
                        sqlBC1.DestinationTableName = tableName1;
                        foreach (DataColumn p in dt_k.Columns)
                        {
                            sqlBC1.ColumnMappings.Add(new SqlBulkCopyColumnMapping(p.ColumnName, p.ColumnName));
                        }
                        sqlBC1.WriteToServer(dt_k);
                    }
                    using (SqlBulkCopy sqlBC2 = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, st))
                    {
                        sqlBC2.SqlRowsCopied += new SqlRowsCopiedEventHandler(SqlRowsCopied);
                        sqlBC2.DestinationTableName = tableName2;
                        foreach (DataColumn p in dt_v.Columns)
                        {
                            sqlBC2.ColumnMappings.Add(new SqlBulkCopyColumnMapping(p.ColumnName, p.ColumnName));
                        }
                        sqlBC2.WriteToServer(dt_v);
                    }
                    st.Commit();
                }
                catch(Exception ex)
                {
                    if(st!=null) st.Rollback();
                    if (ex.Message.Contains("ColumnMapping") || ex.Message.Contains("ColumnName"))
                    {
                        throw new Exception("数据列与表不匹配");
                    }
                    else if (ex.Message.Contains("PRIMARY KEY"))
                    {
                        throw new Exception("该日期的数据已被导入过或主键重复");
                    }
                    else
                    {
                        throw ex;
                    }
                }

            }
        }


        public void Import(IExcel_BaseImportData importdata)
        {
            if (importdata.DtSource != null && importdata.DtSource.Rows.Count > 0)
            {
                bulkCopy.DestinationTableName = importdata.TableName;
                bulkCopy.ColumnMappings.Clear();
                //映射
                foreach (KeyValuePair<string, string> kv in importdata.DicColumn)
                {
                    bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(
                       kv.Key, kv.Value));
                }
                try
                {
                    bulkCopy.WriteToServer(importdata.DtSource);
                    tran.Commit();
                }
                catch (Exception ex)
                {
                    tran.Rollback();
                    if (ex.Message.Contains("ColumnMapping") || ex.Message.Contains("ColumnName"))
                    {
                        throw new Exception("数据列与表不匹配");
                    }
                    else if (ex.Message.Contains("PRIMARY KEY"))
                    {
                        throw new Exception("该日期的数据已被导入过或主键重复");
                    }
                    else if (ex.Message.Contains("数据源"))
                    {
                        DealExceptionNone(importdata);
                    }
                    else
                    {
                        throw ex;
                    }
                }
                finally
                {
                    Close();
                }
            }
        }

        /// <summary>
        /// 错误记录
        /// </summary>
        /// <param name="importdata"></param>
        private void DealExceptionNone(IExcel_BaseImportData importdata)
        {
            //错误行索引
            int errorIndex = Convert.ToInt32(rowsCopiedId);
            //保存错误行
            DataRow err = importdata.DtSource.Rows[errorIndex];//ErrorInfo
            rowsCopiedId = 0;
            throw new Exception("第" + (Convert.ToInt32(err["index"]) + 1) + "行数据出现错误");
        }
    }
}
